﻿using System.Collections.Generic;
using System.Data;
using System.Linq;
using Marta.buchalska.wesele.Admin;

namespace Marta.buchalska.wesele.Model
{
    public class PhotoManager
    {
        internal void CreateCategory(string categoryName)
        {
            var command = string.Format(@"INSERT INTO dbo.Photo_category (Name) VALUES ('{0}')", categoryName);
            new Model.DataBaseGateway().ExecuteComemmand(command);
        }

        internal DataSet GetPhotos()
        {
            var command = string.Format(@"SELECT photo_id, photo_category_name, photo_category_id, photo_gallery_name, photo_gallery_id, photo_description, photo_url, photo_name FROM View_Photo ");
            return new Model.DataBaseGateway().ExecuteComemmand(command);
        }

        internal DataSet GetAllGallery()
        {
            //string command = @"SELECT gallery_id, category_name + '/' + gallery_name AS gallery_name FROM View_category_photo";
            const string command = @"SELECT gallery_id, category_name + '/' + gallery_name AS gallery_name FROM View_category_photo WHERE (gallery_name IS NOT NULL)";
            return new Model.DataBaseGateway().ExecuteComemmand(command);
        }

        internal DataSet GetAllCategory()
        {
            const string command = @"SELECT ID, Name FROM dbo.Photo_category";
            return new Model.DataBaseGateway().ExecuteComemmand(command);
        }

        internal void CreateGallery(string galleryName, int categoryId)
        {
            var command = string.Format(@"INSERT INTO dbo.Photo_gallery (Name, Photo_category_id) VALUES ('{0}',{1})", galleryName, categoryId);
            new Model.DataBaseGateway().ExecuteComemmand(command);
        }

        internal void InsertImage(string name, string desc, string url, int id)
        {
            var command = string.Format(@"INSERT INTO dbo.Photo (Name, Description, URL, Photo_gallery_id) VALUES ('{0}','{1}','{2}',{3})",
                name, desc, url, id);
            new DataBaseGateway().ExecuteComemmand(command);
        }

        public string GetCategoryNameByID(int galleryId)
        {
            var command = string.Format(@"Select name from Photo_gallery where id =" + galleryId);
            var data = new DataBaseGateway().ExecuteComemmand(command);
            return data.Tables[0].Rows[0]["name"] as string;
        }

        internal DataSet GetPhotosFromGallery(int galleryId)
        {
            var command = string.Format(@"SELECT photo_id, photo_category_name, photo_category_id, photo_gallery_name, photo_gallery_id, photo_description, photo_url, photo_name FROM View_Photo Where photo_gallery_id = {0}", galleryId);
            return new Model.DataBaseGateway().ExecuteComemmand(command);
        }

        internal void DeleteGallery(int idGallery)
        {
            var photosInGallery = GetPhotosFromGallery(idGallery);

            var filePath = (from DataRow photo in photosInGallery.Tables[0].Rows select photo["photo_url"] as string).ToList();
            new FileMeneger().DeleteFiles(filePath);
            var command = string.Format(@"DELETE FROM dbo.Photo Where Photo_gallery_id = {0}", idGallery);
            new Model.DataBaseGateway().ExecuteComemmand(command);
            command = string.Format(@"DELETE FROM dbo.Photo_gallery Where Id = {0}", idGallery);
            new Model.DataBaseGateway().ExecuteComemmand(command);
        }

        public void DeletePhoto(int photoId)
        {
            string command;
            
            command = string.Format(@"SELECT URL FROM dbo.Photo where ID = {0}",photoId);
            var dataSet = new DataBaseGateway().ExecuteComemmand(command);
            var filePath = dataSet.Tables[0].Rows[0]["URL"] as string;
            new FileMeneger().DeleteFile(filePath);

            command = string.Format(@"DELETE FROM dbo.Photo Where ID = {0}", photoId);
            new Model.DataBaseGateway().ExecuteComemmand(command);
        }
    }
}